package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.City;
import com.wanmait.travel.vo.Province;

public class ProvinceCityDAO {
	private static ProvinceCityDAO provinceCityDAO;
	public static ProvinceCityDAO getDAO() {
		if(provinceCityDAO==null) {
			provinceCityDAO=new ProvinceCityDAO();
			return provinceCityDAO;
		}
		return provinceCityDAO;
	}
	private ProvinceCityDAO() {}
	
	public List<Province> provinceSearch() {
		String sql="SELECT * FROM province ";
		List<Province> params=new ArrayList<Province>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Province province=new Province();
				province.setId(rs.getInt("id"));
				province.setName(rs.getString("name"));
				params.add(province);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return params;
	}
	
	public List<City> citySearch() {
		String sql="SELECT * FROM city ";
		ArrayList<City> params=new ArrayList<City>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
		
			rs=ps.executeQuery();
			while(rs.next()) {
				City city=new City();
				city.setId(rs.getInt("id"));
				city.setName(rs.getString("name"));
				params.add(city);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return params;
	}
	
	public List<City> cityAllSearch(int cityId) {
		String sql="SELECT * FROM city where province_id=?";
		ArrayList<City> params=new ArrayList<City>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, cityId);
			rs=ps.executeQuery();
			while(rs.next()) {
				City city=new City();
				city.setId(rs.getInt("id"));
				city.setName(rs.getString("name"));
				params.add(city);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return params;
	}
}
